﻿using System.Data;
using System.Data.SQLite;

namespace common
{
    public class SqliteHelper
    {
        public static string strConnString = "";

        protected static string GetConnString()
        {
            if (strConnString != "")
            {
                return strConnString;
            }

            string connectionString = "data source=test.db";
            return connectionString;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SQLiteDataReader getreader(string sql)
        {
            SQLiteConnection con = new SQLiteConnection(GetConnString());
            con.Open();
            SQLiteCommand com = new SQLiteCommand();

            com.Connection = con;
            com.CommandText = sql;

            SQLiteDataReader thisReader = com.ExecuteReader();

            return thisReader;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql)
        {
            SQLiteConnection con = new SQLiteConnection(GetConnString());
            
            SQLiteCommand com = new SQLiteCommand();

            com.Connection = con;
            com.CommandText = sql;
            SQLiteDataAdapter ada = new SQLiteDataAdapter();
            ada.SelectCommand = com;
            DataSet ds = new DataSet();
            ada.Fill(ds);
            com.Dispose();

            DataTable dt = new DataTable();
            dt = ds.Tables[0];
            return dt;
        }

        public static int ExecuteNonQuery(string Query)
        {
            int Result;

            SQLiteConnection con = new SQLiteConnection(GetConnString());
            con.Open();
            SQLiteCommand com = new SQLiteCommand(Query, con);
            Result = com.ExecuteNonQuery();
            con.Close();
            return Result;
        }

        public static object ExecuteAlone(string Query)
        {
            object Result;

            SQLiteConnection con = new SQLiteConnection(GetConnString());
            con.Open();

            SQLiteCommand com = new SQLiteCommand(Query, con);
            Result = com.ExecuteScalar();
            con.Close();

            return Result;
        }
    }
}